/**
 * @author Mie Plougstrup, Anders Abildgaard, Bo Stokholm 
 * @Version 16-12-2014
 */

package dbLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.Booking;
import modelLayer.Customer;

public class DbBooking {

	private Connection con;
	private DbBusinessPartner dbBusinessPartner;
	private DbAddress dbAddress;

	/**
	 * Constructor - Initializes fields.
	 */
	public DbBooking(){
		con = DbConnection.getInstance().getDBcon();
		dbBusinessPartner = new DbBusinessPartner();
		dbAddress = new DbAddress();
	}

	/**
	 * Method for retrieving a booking object by its id.
	 * 
	 * @param int bookingId
	 * @return Booking
	 */
	public Booking retrieveById(int bookingId) {
		return singleWhere("bookingId = " + bookingId , true);
	}
	
	/**
	 * Method for retrieving the nondisposed bookings.
	 * 
	 * @return
	 */
	public ArrayList<Booking> retrieveNonDisposed() {
		return multiWhere("not exists (select * from bookingsequence where booking.bookingid = bookingsequence.bookingId) ORDER BY deliveryDate", true);
	}
	
	public ArrayList<Booking> retrieveAll() {
		return multiWhere("", true);
	}

	/**
	 * Method for updating a booking object in the database.
	 * 
	 * @param Booking booking
	 * @return int (rc)
	 */
	public int update(Booking booking) {
		int ra = -1;
		String query = "Update Booking SET " +
				"bookingNo =" + booking.getBookingNo()
				+ ", customerRefNo =" + booking.getCustomerRefNo()
				+ ", storage =" + booking.getStorage()
				+ ", packageType =" + booking.getPackageType()
				+ ", quantity =" + booking.getQuantity()
				+ ", price =" + booking.getPrice()
				+ ", weight =" + booking.getWeight()
				+ ", length =" + booking.getLength()
				+ ", width =" + booking.getWidth()
				+ ", height =" + booking.getHeight()
				+ ", customerId =" + booking.getCustomer().getPartnerId()
				+ ", pickUpDate ='" + booking.getPickUpDate().toString()
				+ "', deliveryDate ='" + booking.getDeliveryDate().toString() + "'"
				+ " Where bookingId =" + booking.getBookingId();

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e) {}
		return ra;
	}

	/**
	 * Method for deleting a booking object from the database.
	 * 
	 * @param Booking booking
	 * @return int (rc)
	 */
	public int delete(Booking booking) {
		int ra = -1;

		String query = "Delete from booking Where bookingId =" + booking.getBookingId();
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ra = stmt.executeUpdate(query);
			stmt.close();
		} catch(Exception e){}
		return ra;
	}

	/**
	 * Method for inserting a booking object into the database.
	 * @param Booking bObj
	 * @return int (ra)
	 * @throws Exception
	 */
	public int insert(Booking bObj) throws Exception {
		int ra = -1;
		String query = "Insert into booking(bookingId, bookingNo, customerRefNo, storage, packageType, quantity, price, weight, length, width, height, customerId, pickUpDate, deliveryDate) "
				+ "Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try{
			PreparedStatement prpstmt = con.prepareStatement(query);
			prpstmt.setInt(1, bObj.getBookingId());
			prpstmt.setInt(2, bObj.getBookingNo());
			prpstmt.setInt(3, bObj.getCustomerRefNo());
			prpstmt.setInt(4, bObj.getStorage());
			prpstmt.setInt(5, bObj.getPackageType());
			prpstmt.setInt(6, bObj.getQuantity());
			prpstmt.setDouble(7, bObj.getPrice());
			prpstmt.setDouble(8, bObj.getWeight());
			prpstmt.setDouble(9, bObj.getLength());
			prpstmt.setDouble(10, bObj.getWidth());
			prpstmt.setDouble(11, bObj.getHeight());
			prpstmt.setInt(12, bObj.getCustomer().getPartnerId());
			prpstmt.setDate(13, bObj.getPickUpDate());
			prpstmt.setDate(14, bObj.getDeliveryDate());
			prpstmt.setQueryTimeout(5);
			ra = prpstmt.executeUpdate();
			prpstmt.close();
		} catch(Exception e) {
			throw new Exception("Booking not inserted correct");
		}
		return ra;
	}

	/**
	 * Method for creating a single booking object, based on a where clause.
	 * @param String wClause
	 * @param boolean retrieveAssociation
	 * @return Booking bObj
	 */
	private Booking singleWhere(String wClause, boolean retrieveAssociation){
		ResultSet results;
		Booking bObj = new Booking();

		String query = buildQuery(wClause);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if(results.next()) {
				bObj = buildBooking(results);
				if(retrieveAssociation) {
					bObj.setCustomer(dbBusinessPartner.retrieveCustomerById(bObj.getCustomer().getPartnerId()));
					bObj.setAddressList(dbAddress.retrieveForBooking(bObj.getBookingId()));
				}	
				stmt.close();
			}
		} catch(Exception e) {}
		return bObj;
	}

	/**
	 * Method for creating multiple booking objects based on a where clause.
	 * 
	 * @param String wClause
	 * @param boolean retrieveAssociation
	 * @return ArrayList<Booking> list
	 */
	private ArrayList<Booking> multiWhere(String wClause, boolean retrieveAssociation){
		ResultSet results;
		ArrayList<Booking> bList = new ArrayList<Booking>();

		String query = buildQuery(wClause);
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next()) {
				bList.add(buildBooking(results));
			}	
			if(retrieveAssociation) {
				for(Booking bObj : bList) {
					bObj.setCustomer(dbBusinessPartner.retrieveCustomerById(bObj.getCustomer().getPartnerId()));
					bObj.setAddressList(dbAddress.retrieveForBooking(bObj.getBookingId()));
				}	
			}
			stmt.close();
		} catch(Exception e) {
			System.out.println(e);
		}
		return bList;
	}

	/**
	 * Method for creating the queries used with the database.
	 * 
	 * @param String wClause
	 * @return String query
	 */
	private String buildQuery(String wClause){
		String query = "SELECT bookingId, bookingNo, customerRefNo, customerId, pickUpDate, deliveryDate, storage, packageType, quantity, price, weight, length, width, height FROM booking";

		if(wClause.length() > 0 ) {
			query = query + " WHERE " + wClause;
		}
		return query;
	}

	/**
	 * Method for building the booking objects from the data acquired through the database.
	 * 
	 * @param ResultSet results
	 * @return Booking bObj
	 */
	private Booking buildBooking(ResultSet results) {
		Booking bObj = new Booking();

		try {
			bObj.setBookingId(results.getInt("bookingId"));
			bObj.setBookingNo(results.getInt("bookingNo"));
			bObj.setCustomerRefNo(results.getInt("customerRefNo"));
			bObj.setStorage(results.getInt("storage"));
			bObj.setPackageType(results.getInt("packageType"));
			bObj.setQuantity(results.getInt("quantity"));
			bObj.setPrice(results.getDouble("price"));
			bObj.setWeight(results.getDouble("weight"));
			bObj.setLength(results.getDouble("length"));
			bObj.setWidth(results.getDouble("width"));
			bObj.setHeight(results.getDouble("height"));
			bObj.setPickUpDate(results.getDate("pickUpDate"));
			bObj.setDeliveryDate(results.getDate("deliveryDate"));
			bObj.setCustomer(new Customer(results.getInt("customerId")));

		} catch(Exception e) {}
		return bObj;
	}
}
